﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SachOnlineLibrary.DAL
{
    internal class ThongKeDAL
    {
        KetNoiDB ketnoidb;

        public ThongKeDAL()
        {
            ketnoidb = new KetNoiDB();
        }

        internal DataTable ThongKeSachBanChay(int month, int year)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 5 TenSach, Sum(CHITIETPDH.SoLuong) as SoLuongBan from CHITIETPDH, Sach, PHIEUDATHANG " +
                                "where CHITIETPDH.MaSach=Sach.MaSach and CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year group by Sach.MaSach, TenSach order by Sum(CHITIETPDH.SoLuong) desc";
            cmd.Parameters.Add("@month",SqlDbType.Int).Value=month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value=year;
            return ketnoidb.ExecuteQuery(cmd);
        }

        internal DataTable ThongKeLoaiSachBanChay(int month, int year)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 5 TenLoai, Sum(CHITIETPDH.SoLuong) as SoLuongBan from CHITIETPDH, Sach, Loai, PHIEUDATHANG " +
                                "where CHITIETPDH.MaSach=Sach.MaSach and CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and Sach.MaLoai=Loai.MaLoai and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year group by Loai.MaLoai, TenLoai order by Sum(CHITIETPDH.SoLuong) desc";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = year;
            return ketnoidb.ExecuteQuery(cmd);
        }

        internal string SachMuaNhieuNhat(DateTime date)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 1 TenSach, Sum(CHITIETPDH.SoLuong) from CHITIETPDH, Sach, phieudathang " +
                                "where CHITIETPDH.MaSach=Sach.MaSach and CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year group by TenSach order by Sum(CHITIETPDH.SoLuong)";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = date.Month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = date.Year;
            return ketnoidb.ExecuteScalar(cmd);
        }

        internal string LoaiMuaNhieuNhat(DateTime date)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 1 tenloai, Sum(CHITIETPDH.SoLuong) from CHITIETPDH, Sach, loai, phieudathang " +
                                "where CHITIETPDH.MaSach=Sach.MaSach and CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and sach.maloai = loai.maloai and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year group by tenloai order by Sum(CHITIETPDH.SoLuong)";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = date.Month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = date.Year;
            return ketnoidb.ExecuteScalar(cmd);
        }

        internal string KhachHangMuaNhieuNhat(DateTime date)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select top 1 tentk , Sum(CHITIETPDH.SoLuong) from CHITIETPDH, taikhoan, phieudathang " +
                                "where phieudathang.tkid=taikhoan.tkid and CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year group by tentk order by Sum(CHITIETPDH.SoLuong)";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = date.Month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = date.Year;
            return ketnoidb.ExecuteScalar(cmd);
        }

        internal long TongDoanhThu(DateTime date)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select Sum(CHITIETPDH.SoLuong + CHITIETPDH.dongia) from CHITIETPDH, phieudathang " +
                                "where CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = date.Month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = date.Year;
            return long.Parse(ketnoidb.ExecuteScalar(cmd));
        }

        internal int TongSoLuong(DateTime date)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select Sum(CHITIETPDH.SoLuong) from CHITIETPDH, phieudathang " +
                                "where CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = date.Month;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = date.Year;
            return int.Parse(ketnoidb.ExecuteScalar(cmd));
        }

        internal DataTable ThongKeKhachHang(int thang, int nam)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select ( Year(getdate()) -year(NgaySinh)) as Tuoi, Sum(CHITIETPDH.SoLuong) as SoLuongMua from CHITIETPDH, TaiKhoan, PHIEUDATHANG " +
                                "where PhieuDatHang.TKID=TaiKhoan.TKID and CHITIETPDH.MaPhieu=PHIEUDATHANG.MaPhieu and PHIEUDATHANG.TrangThai = N'Đã giao' and " +
                                "Month(NgayDatHang)=@month and Year(NgayDatHang)=@year group by ( Year(getdate()) -year(NgaySinh)) order by Sum(CHITIETPDH.SoLuong) desc";
            cmd.Parameters.Add("@month", SqlDbType.Int).Value = thang;
            cmd.Parameters.Add("@year", SqlDbType.Int).Value = nam;
            return ketnoidb.ExecuteQuery(cmd);
        }
    }
}
